﻿using System;
using System.Data;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace BrsdServer
{
    public class userInfoBLL
    {
        MySqlDBHelper db = new MySqlDBHelper();

        public List<user_infoEntity> LoadAll()
        {
            string strSql = "select *from user_info";
            List<user_infoEntity> list = new List<user_infoEntity>();

            MySqlCommand cmd=db.GetSqlStringCommond(strSql);
            MySqlDataReader dr=db.ExecuteReader(cmd);
            while(dr.Read())
            {
                user_infoEntity user_info=new user_infoEntity();
                user_info.user_id = dr.GetInt32(0);
                user_info.user_name = dr.GetString(1);
                user_info.user_pwd = dr.GetString(2);
                user_info.user_privilege = dr.GetInt32(3);
                user_info.user_lastlogin = dr.GetString(4);
                user_info.user_registertime = dr.GetString(5);
                user_info.user_email = dr.GetString(6);
                user_info.user_tel = dr.GetString(7);
                user_info.user_loction = dr.GetString(8);
                list.Add(user_info);
            }
            return list;
        }

        public DataTable LoadAllDataTable()
        {
            string strSql = "select *from user_info";

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            return dt;
        }

        public DataTable LoadAllDataTable(String userName)
        {
            string strSql = "select *from user_info where user_name='"+userName+"'";

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            return dt;
        }

        public int GetUser(string user)
        {
            string strSql = "select *from user_info where user_name='" + user + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            int userExist=Convert.ToInt32(db.ExecuteScalar(cmd));
            return userExist;
        }

        public int GetUserKind(String user, String pwd)
        {
            int userKind = -1;
            string strSql = "select user_privilege from user_info where user_name='" + user + "' and user_password='" + pwd + "'";

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            MySqlDataReader dr=db.ExecuteReader(cmd);
            dr.Read();
            userKind=dr.GetInt32(0);
            dr.Close();
            return userKind;
        }

        public int GetUserID(String user, String pwd)
        {
            int user_id = 0;
            string strSql = "select user_id from user_info where user_name='" + user + "' and user_password='" + pwd + "'";

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            MySqlDataReader dr = db.ExecuteReader(cmd);
            dr.Read();
            user_id = dr.GetInt32(0);
            dr.Close();
            return user_id;
        }

        public int Insert(user_infoEntity obj)
        {
            string strSql="INSERT INTO user_info(user_name,user_password,"+
                "user_privilege,user_lastlogin,user_registertime,user_email,"+
                "user_tel,user_location) VALUES('"+obj.user_name+"','"+obj.user_pwd+"',"+
                obj.user_privilege+",'"+obj.user_lastlogin+"','"+obj.user_registertime+"','"+
                obj.user_email+"','"+obj.user_tel+"','"+obj.user_loction+"')";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            return db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 根据用户名和密码查找数据库是否存在该记录。
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="pwd">密码</param>
        /// <returns>1：成功找到，0：没有找到</returns>
        public int FindByUserAndPwd(String userName, String pwd)
        {
            //SELECT *from user_info where user_name='Admin' AND user_password='888888'
            String strSql="select *from user_info where user_name='"+userName+"' and user_password='"+pwd+"'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            if (db.ExecuteScalar(cmd)==null)
            {
                return 0;
            }
            else
            {
                return 1;
            }
        }

        /// <summary>
        /// 根据用户名和密码来得到记录。
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public DataTable LoadAllByUserAndPwnd(String userName, String pwd)
        {
            String strSql = "select *from user_info where user_name='" + userName + "' and user_password='" + pwd + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 根据用户名和密码来更新数据。
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int Update(String userName, String pwd,String newPwd,String email,String tel,String addr)
        {
            String strSql = "UPDATE user_info set user_password='" + newPwd + "',user_email='" +
                email+"',user_tel='"+tel+"',user_location='"+addr+"' WHERE user_name='"+
                userName+"' and user_password='"+pwd+"'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql); 
            return (db.ExecuteNonQuery(cmd));
        }

        public int DeleteUser(string userName)
        {
            string strSql = "delete from user_info where user_name='" + userName + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            int ret = db.ExecuteNonQuery(cmd);
            return ret;
        }
    }
}
